<!DOCTYPE html>
<html lang="en"><head><script src="/livereload.js?mindelay=10&amp;v=2&amp;port=1313&amp;path=livereload" data-no-instant defer></script>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0" />
  <meta name="robots" content="noindex, nofollow" />
  <link rel="icon shortcut" href="/favicon.ico" sizes="32x32" />
<link rel="icon" href="/favicon.svg" type="image/svg+xml" />
<link rel="icon" href="/favicon-dark.svg" type="image/svg+xml" media="(prefers-color-scheme: dark)" />
<link rel="icon" href="/favicon-16x16.png" type="image/png" sizes="16x16" />
<link rel="icon" href="/favicon-32x32.png" type="image/png" sizes="32x32" />
<link rel="apple-touch-icon" href="/apple-touch-icon.png" sizes="180x180" />
<link fetchpriority="low" href="/site.webmanifest" rel="manifest" />

  <title>第 29 章：BRIN – PostgreSQL 14 Internals</title>
  <meta name="description" content="29.1 总览 与为快速找到所需行的其他索引不同，BRIN 1 旨在过滤掉不必要的行。BRIN 访问方法主要用于几个 TB 及以上的大表，因此较小的索引尺寸优于搜索精度。
为了加快搜索，整个表被划分为多个范围，因此得名：块范围索引。每个范围包含若干个页面。索引不存储 TIDS，只保留每个范围的摘要信息。对于 ordinal 数据类型，在最简单的情况下，是最小值和最大值，但不同的操作符类可能会收集范围中值的不同信息。
范围中的页面数量在创建索引时根据 pages_per_range 存储参数值进行定义。
如果查询条件引用了索引列，那么所有保证没有匹配项的范围都可以跳过。索引将以有损位图的形式返回其他所有范围的页面；这些页面的所有行都必须重新检查。
因此，BRIN 非常适合具有局部化值的列 (即，存储的值彼此靠近的列具有相似的摘要信息属性) 。对于 ordinal 数据类型，这意味着值必须按升序或降序存储，也就是说，它们的物理位置与&#34;大于&#34;和&#34;小于&#34;操作定义的逻辑顺序之间具有很高的相关性。对于其他类型的摘要信息，“相似属性&#34;可能有所不同。
BRIN 更像是顺序堆扫描的加速器，而不是传统意义上的索引。它可以看作是分区的一种替代方案，每个范围代表一个虚拟分区。
29.2 样例 我们的示例数据库中没有足够大到需要使用 BRIN 索引的表，但我们可以想象一下，分析报告要求我们拥有一张非规范化表，里面包含特定机场所有出发和到达航班的汇总信息，甚至到具体座位的占用情况。每个机场的数据都会在相应时区午夜时每日更新。添加的数据不会被更新或删除。
这张表如下所示：
CREATE TABLE flights_bi( airport_code char(3), airport_coord point, -- airport coordinates airport_utc_offset interval, -- timezone flight_no char(6), flight_type text, -- departure or arrival scheduled_time timestamptz, actual_time timestamptz, aircraft_code char(3), seat_no varchar(4), fare_conditions varchar(10), -- travel class passenger_id varchar(20), passenger_name text ); 数据加载可以通过嵌套循环来模拟：2 外循环对应于天数 (示例数据库中存储的是年度数据)，内循环则基于时区。因此，即使在循环中数据没有显式地进行排序，加载后的数据在时间和机场方面或多或少是有序的。" />

  
    <link rel="canonical" href="//localhost:1313/docs/chapter29/" itemprop="url" />
  

  

<meta property="og:title" content="第 29 章：BRIN" />
<meta property="og:description" content="" />
<meta property="og:type" content="website" />
<meta property="og:url" content="//localhost:1313/docs/chapter29/" />

  
  <meta itemprop="name" content="第 29 章：BRIN">
  <meta itemprop="description" content="29.1 总览 与为快速找到所需行的其他索引不同，BRIN 1 旨在过滤掉不必要的行。BRIN 访问方法主要用于几个 TB 及以上的大表，因此较小的索引尺寸优于搜索精度。
为了加快搜索，整个表被划分为多个范围，因此得名：块范围索引。每个范围包含若干个页面。索引不存储 TIDS，只保留每个范围的摘要信息。对于 ordinal 数据类型，在最简单的情况下，是最小值和最大值，但不同的操作符类可能会收集范围中值的不同信息。
范围中的页面数量在创建索引时根据 pages_per_range 存储参数值进行定义。
如果查询条件引用了索引列，那么所有保证没有匹配项的范围都可以跳过。索引将以有损位图的形式返回其他所有范围的页面；这些页面的所有行都必须重新检查。
因此，BRIN 非常适合具有局部化值的列 (即，存储的值彼此靠近的列具有相似的摘要信息属性) 。对于 ordinal 数据类型，这意味着值必须按升序或降序存储，也就是说，它们的物理位置与&#34;大于&#34;和&#34;小于&#34;操作定义的逻辑顺序之间具有很高的相关性。对于其他类型的摘要信息，“相似属性&#34;可能有所不同。
BRIN 更像是顺序堆扫描的加速器，而不是传统意义上的索引。它可以看作是分区的一种替代方案，每个范围代表一个虚拟分区。
29.2 样例 我们的示例数据库中没有足够大到需要使用 BRIN 索引的表，但我们可以想象一下，分析报告要求我们拥有一张非规范化表，里面包含特定机场所有出发和到达航班的汇总信息，甚至到具体座位的占用情况。每个机场的数据都会在相应时区午夜时每日更新。添加的数据不会被更新或删除。
这张表如下所示：
CREATE TABLE flights_bi( airport_code char(3), airport_coord point, -- airport coordinates airport_utc_offset interval, -- timezone flight_no char(6), flight_type text, -- departure or arrival scheduled_time timestamptz, actual_time timestamptz, aircraft_code char(3), seat_no varchar(4), fare_conditions varchar(10), -- travel class passenger_id varchar(20), passenger_name text ); 数据加载可以通过嵌套循环来模拟：2 外循环对应于天数 (示例数据库中存储的是年度数据)，内循环则基于时区。因此，即使在循环中数据没有显式地进行排序，加载后的数据在时间和机场方面或多或少是有序的。">
  <meta itemprop="wordCount" content="1960">
  <meta name="twitter:card" content="summary">
  <meta name="twitter:title" content="第 29 章：BRIN">
  <meta name="twitter:description" content="29.1 总览 与为快速找到所需行的其他索引不同，BRIN 1 旨在过滤掉不必要的行。BRIN 访问方法主要用于几个 TB 及以上的大表，因此较小的索引尺寸优于搜索精度。
为了加快搜索，整个表被划分为多个范围，因此得名：块范围索引。每个范围包含若干个页面。索引不存储 TIDS，只保留每个范围的摘要信息。对于 ordinal 数据类型，在最简单的情况下，是最小值和最大值，但不同的操作符类可能会收集范围中值的不同信息。
范围中的页面数量在创建索引时根据 pages_per_range 存储参数值进行定义。
如果查询条件引用了索引列，那么所有保证没有匹配项的范围都可以跳过。索引将以有损位图的形式返回其他所有范围的页面；这些页面的所有行都必须重新检查。
因此，BRIN 非常适合具有局部化值的列 (即，存储的值彼此靠近的列具有相似的摘要信息属性) 。对于 ordinal 数据类型，这意味着值必须按升序或降序存储，也就是说，它们的物理位置与&#34;大于&#34;和&#34;小于&#34;操作定义的逻辑顺序之间具有很高的相关性。对于其他类型的摘要信息，“相似属性&#34;可能有所不同。
BRIN 更像是顺序堆扫描的加速器，而不是传统意义上的索引。它可以看作是分区的一种替代方案，每个范围代表一个虚拟分区。
29.2 样例 我们的示例数据库中没有足够大到需要使用 BRIN 索引的表，但我们可以想象一下，分析报告要求我们拥有一张非规范化表，里面包含特定机场所有出发和到达航班的汇总信息，甚至到具体座位的占用情况。每个机场的数据都会在相应时区午夜时每日更新。添加的数据不会被更新或删除。
这张表如下所示：
CREATE TABLE flights_bi( airport_code char(3), airport_coord point, -- airport coordinates airport_utc_offset interval, -- timezone flight_no char(6), flight_type text, -- departure or arrival scheduled_time timestamptz, actual_time timestamptz, aircraft_code char(3), seat_no varchar(4), fare_conditions varchar(10), -- travel class passenger_id varchar(20), passenger_name text ); 数据加载可以通过嵌套循环来模拟：2 外循环对应于天数 (示例数据库中存储的是年度数据)，内循环则基于时区。因此，即使在循环中数据没有显式地进行排序，加载后的数据在时间和机场方面或多或少是有序的。">

    <link href="/css/compiled/main.css" rel="stylesheet" />



  <link href="/css/custom.css" rel="stylesheet" />





  <script>
     
    const defaultTheme = 'light';

    const setDarkTheme = () => {
      document.documentElement.classList.add("dark");
      document.documentElement.style.colorScheme = "dark";
    }
    const setLightTheme = () => {
      document.documentElement.classList.remove("dark");
      document.documentElement.style.colorScheme = "light";
    }

    if ("color-theme" in localStorage) {
      localStorage.getItem("color-theme") === "dark" ? setDarkTheme() : setLightTheme();
    } else {
      defaultTheme === "dark" ? setDarkTheme() : setLightTheme();
      if (defaultTheme === "system") {
        window.matchMedia("(prefers-color-scheme: dark)").matches ? setDarkTheme() : setLightTheme();
      }
    }
  </script>

  
</head>
<body dir="ltr"><div class="nav-container hx-sticky hx-top-0 hx-z-20 hx-w-full hx-bg-transparent print:hx-hidden">
  <div class="nav-container-blur hx-pointer-events-none hx-absolute hx-z-[-1] hx-h-full hx-w-full hx-bg-white dark:hx-bg-dark hx-shadow-[0_2px_4px_rgba(0,0,0,.02),0_1px_0_rgba(0,0,0,.06)] contrast-more:hx-shadow-[0_0_0_1px_#000] dark:hx-shadow-[0_-1px_0_rgba(255,255,255,.1)_inset] contrast-more:dark:hx-shadow-[0_0_0_1px_#fff]"></div>

  <nav class="hx-mx-auto hx-flex hx-items-center hx-justify-end hx-gap-2 hx-h-16 hx-px-6 hx-max-w-[90rem]">
    <a class="hx-flex hx-items-center hover:hx-opacity-75 ltr:hx-mr-auto rtl:hx-ml-auto" href="/">
        <img class="hx-block dark:hx-hidden" src="/images/postgresql-elephant-in-potential.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <img class="hx-hidden dark:hx-block" src="/images/postgresql-elephant-in-power.svg" alt="PostgreSQL 14 Internals" height="30" width="45" />
        <span class="hx-mx-2 hx-font-extrabold hx-inline hx-select-none" title="PostgreSQL 14 Internals">PostgreSQL 14 Internals</span>
    </a><a
            title="Docs"
            href="/docs"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-font-medium"
          >
            <span class="hx-text-center">Docs</span>
          </a><a
            title="About"
            href="/about"
            
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">About</span>
          </a><a
            title="Contact ↗"
            href="https://github.com/xiongcccc"
            target="_blank" rel="noreferer"
            class="hx-text-sm contrast-more:hx-text-gray-700 contrast-more:dark:hx-text-gray-100 hx-relative -hx-ml-2 hx-hidden hx-whitespace-nowrap hx-p-2 md:hx-inline-block hx-text-gray-600 hover:hx-text-gray-800 dark:hx-text-gray-400 dark:hover:hx-text-gray-200"
          >
            <span class="hx-text-center">Contact ↗</span>
          </a><div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://github.com/xiongcccc/xiongcccc.github.io" title="GitHub"><svg height=24 fill="currentColor" viewBox="3 3 18 18">
  <path d="M12 3C7.0275 3 3 7.12937 3 12.2276C3 16.3109 5.57625 19.7597 9.15374 20.9824C9.60374 21.0631 9.77249 20.7863 9.77249 20.5441C9.77249 20.3249 9.76125 19.5982 9.76125 18.8254C7.5 19.2522 6.915 18.2602 6.735 17.7412C6.63375 17.4759 6.19499 16.6569 5.8125 16.4378C5.4975 16.2647 5.0475 15.838 5.80124 15.8264C6.51 15.8149 7.01625 16.4954 7.18499 16.7723C7.99499 18.1679 9.28875 17.7758 9.80625 17.5335C9.885 16.9337 10.1212 16.53 10.38 16.2993C8.3775 16.0687 6.285 15.2728 6.285 11.7432C6.285 10.7397 6.63375 9.9092 7.20749 9.26326C7.1175 9.03257 6.8025 8.08674 7.2975 6.81794C7.2975 6.81794 8.05125 6.57571 9.77249 7.76377C10.4925 7.55615 11.2575 7.45234 12.0225 7.45234C12.7875 7.45234 13.5525 7.55615 14.2725 7.76377C15.9937 6.56418 16.7475 6.81794 16.7475 6.81794C17.2424 8.08674 16.9275 9.03257 16.8375 9.26326C17.4113 9.9092 17.76 10.7281 17.76 11.7432C17.76 15.2843 15.6563 16.0687 13.6537 16.2993C13.98 16.5877 14.2613 17.1414 14.2613 18.0065C14.2613 19.2407 14.25 20.2326 14.25 20.5441C14.25 20.7863 14.4188 21.0746 14.8688 20.9824C16.6554 20.364 18.2079 19.1866 19.3078 17.6162C20.4077 16.0457 20.9995 14.1611 21 12.2276C21 7.12937 16.9725 3 12 3Z"></path>
</svg>
<span class="hx-sr-only">GitHub</span>
          </a>
          <a class="hx-p-2 hx-text-current" target="_blank" rel="noreferer" href="https://twitter.com/" title="Twitter"><svg height=24 xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path fill="currentColor" d="M389.2 48h70.6L305.6 224.2 487 464H345L233.7 318.6 106.5 464H35.8L200.7 275.5 26.8 48H172.4L272.9 180.9 389.2 48zM364.4 421.8h39.1L151.1 88h-42L364.4 421.8z"/></svg><span class="hx-sr-only">Twitter</span>
          </a><button type="button" aria-label="Menu" class="hamburger-menu -hx-mr-2 hx-rounded hx-p-2 active:hx-bg-gray-400/20 md:hx-hidden"><svg height=24 fill="none" viewBox="0 0 24 24" stroke="currentColor"><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 8H20"></path></g><g><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M4 16H20"></path></g></svg></button>
  </nav>
</div>

  <div class='hx-mx-auto hx-flex hx-max-w-[90rem]'>
    <div class="mobile-menu-overlay [transition:background-color_1.5s_ease] hx-fixed hx-inset-0 hx-z-10 hx-bg-black/80 dark:hx-bg-black/60 hx-hidden"></div>
<aside class="sidebar-container hx-flex hx-flex-col print:hx-hidden md:hx-top-16 md:hx-shrink-0 md:hx-w-64 md:hx-self-start max-md:[transform:translate3d(0,-100%,0)] md:hx-sticky">
  
  <div class="hx-px-4 hx-pt-4 md:hx-hidden">
    <div class="search-wrapper hx-relative md:hx-w-64">
  <div class="hx-relative hx-flex hx-items-center hx-text-gray-900 contrast-more:hx-text-gray-800 dark:hx-text-gray-300 contrast-more:dark:hx-text-gray-300">
    <input
      placeholder="Search..."
      class="search-input hx-block hx-w-full hx-appearance-none hx-rounded-lg hx-px-3 hx-py-2 hx-transition-colors hx-text-base hx-leading-tight md:hx-text-sm hx-bg-black/[.05] dark:hx-bg-gray-50/10 focus:hx-bg-white dark:focus:hx-bg-dark placeholder:hx-text-gray-500 dark:placeholder:hx-text-gray-400 contrast-more:hx-border contrast-more:hx-border-current"
      type="search"
      value=""
      spellcheck="false"
    />
    <kbd
      class="hx-absolute hx-my-1.5 hx-select-none ltr:hx-right-1.5 rtl:hx-left-1.5 hx-h-5 hx-rounded hx-bg-white hx-px-1.5 hx-font-mono hx-text-[10px] hx-font-medium hx-text-gray-500 hx-border dark:hx-border-gray-100/20 dark:hx-bg-dark/50 contrast-more:hx-border-current contrast-more:hx-text-current contrast-more:dark:hx-border-current hx-items-center hx-gap-1 hx-transition-opacity hx-pointer-events-none hx-hidden sm:hx-flex"
    >
      CTRL K
    </kbd>
  </div>

  <div>
    <ul
      class="search-results hextra-scrollbar hx-hidden hx-border hx-border-gray-200 hx-bg-white hx-text-gray-100 dark:hx-border-neutral-800 dark:hx-bg-neutral-900 hx-absolute hx-top-full hx-z-20 hx-mt-2 hx-overflow-auto hx-overscroll-contain hx-rounded-xl hx-py-2.5 hx-shadow-xl hx-max-h-[min(calc(50vh-11rem-env(safe-area-inset-bottom)),400px)] md:hx-max-h-[min(calc(100vh-5rem-env(safe-area-inset-bottom)),400px)] hx-inset-x-0 ltr:md:hx-left-auto rtl:md:hx-right-auto contrast-more:hx-border contrast-more:hx-border-gray-900 contrast-more:dark:hx-border-gray-50 hx-w-screen hx-min-h-[100px] hx-max-w-[min(calc(100vw-2rem),calc(100%+20rem))]"
      style="transition: max-height 0.2s ease 0s;"
    ></ul>
  </div>
</div>

  </div>
  <div class="hextra-scrollbar hx-overflow-y-auto hx-overflow-x-hidden hx-p-4 hx-grow md:hx-h-[calc(100vh-var(--navbar-height)-var(--menu-height))]">
    <ul class="hx-flex hx-flex-col hx-gap-1 md:hx-hidden">
      
      
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/about/"
    
  >About
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/"
    
  >Docs
        <span class="hextra-sidebar-collapsible-button"><svg fill="none" viewBox="0 0 24 24" stroke="currentColor" class="hx-h-[18px] hx-min-w-[18px] hx-rounded-sm hx-p-0.5 hover:hx-bg-gray-800/5 dark:hover:hx-bg-gray-100/5"><path stroke-linecap="round" stroke-linejoin="round" stroke-width="2" d="M9 5l7 7-7 7" class="hx-origin-center hx-transition-transform rtl:-hx-rotate-180"></path></svg></span>
    </a><div class="ltr:hx-pr-0 hx-overflow-hidden">
        <ul class='hx-relative hx-flex hx-flex-col hx-gap-1 before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] ltr:hx-ml-3 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-mr-3 rtl:hx-pr-3 rtl:before:hx-right-0 dark:before:hx-bg-neutral-800'><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a>
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a>
  
    <ul class='hx-flex hx-flex-col hx-gap-1 hx-relative before:hx-absolute before:hx-inset-y-1 before:hx-w-px before:hx-bg-gray-200 before:hx-content-[""] dark:before:hx-bg-neutral-800 ltr:hx-pl-3 ltr:before:hx-left-0 rtl:hx-pr-3 rtl:before:hx-right-0 ltr:hx-ml-3 rtl:hx-mr-3'><li>
              <a
                href="#291-%e6%80%bb%e8%a7%88"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.1 总览</a>
            </li>
          <li>
              <a
                href="#292-%e6%a0%b7%e4%be%8b"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.2 样例</a>
            </li>
          <li>
              <a
                href="#293-%e9%a1%b5%e9%9d%a2%e5%b8%83%e5%b1%80"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.3 页面布局</a>
            </li>
          <li>
              <a
                href="#294-%e6%90%9c%e7%b4%a2"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.4 搜索</a>
            </li>
          <li>
              <a
                href="#295-%e6%91%98%e8%a6%81%e4%bf%a1%e6%81%af%e6%9b%b4%e6%96%b0"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.5 摘要信息更新</a>
            </li>
          <li>
              <a
                href="#296-minmax-%e7%b1%bb"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.6 Minmax 类</a>
            </li>
          <li>
              <a
                href="#297-span-classmarginalia-data-notev-14minmax-multi-%e7%b1%bbspan"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.7 &lt;span class=&#34;marginalia&#34; data-note=&#34;v. 14&#34;&gt;&lt;strong&gt;Minmax-Multi 类&lt;/strong&gt;&lt;/span&gt;</a>
            </li>
          <li>
              <a
                href="#298-inclusion-%e7%b1%bb"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.8 Inclusion 类</a>
            </li>
          <li>
              <a
                href="#299-span-classmarginalia-data-notev-14bloom-%e7%b1%bbspan"
                class="hx-flex hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [word-break:break-word] hx-cursor-pointer [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] contrast-more:hx-border hx-gap-2 before:hx-opacity-25 before:hx-content-['#'] hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:hx-text-gray-900 contrast-more:dark:hx-text-gray-50 contrast-more:hx-border-transparent contrast-more:hover:hx-border-gray-900 contrast-more:dark:hover:hx-border-gray-50"
              >29.9 &lt;span class=&#34;marginalia&#34; data-note=&#34;v. 14&#34;&gt;&lt;strong&gt;Bloom 类&lt;/strong&gt;&lt;/span&gt;</a>
            </li>
          </ul>
  
              
            </li><li class="hx-flex hx-flex-col open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a>
              
            </li></ul>
      </div></li>
          <li class=""><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/recommend/"
    
  >Recommend
    </a></li>
    </ul>

    <ul class="hx-flex hx-flex-col hx-gap-1 max-md:hx-hidden">
        
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter01/"
    
  >第 1 章：介绍
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter02/"
    
  >第 2 章：隔离性
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter03/"
    
  >第 3 章：页与元组
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter04/"
    
  >第 4 章：快照
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter05/"
    
  >第 5 章：页剪枝与 HOT 更新
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter06/"
    
  >第 6 章：Vacuum 与 Autovacuum
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter07/"
    
  >第 7 章：冻结
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter08/"
    
  >第 8 章：重建表与索引
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter09/"
    
  >第 9 章：缓冲区缓存
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter10/"
    
  >第 10 章：预写式日志
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter11/"
    
  >第 11 章：WAL 模式
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter12/"
    
  >第 12 章：关系级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter13/"
    
  >第 13 章：行级锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter14/"
    
  >第 14 章：多样的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter15/"
    
  >第 15 章：内存结构上的锁
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter16/"
    
  >第 16 章：查询执行阶段
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter17/"
    
  >第 17 章：统计信息
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter18/"
    
  >第 18 章：表访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter19/"
    
  >第 19 章：索引访问方法
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter20/"
    
  >第 20 章：索引扫描
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter21/"
    
  >第 21 章：嵌套循环
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter22/"
    
  >第 22 章：哈希
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter23/"
    
  >第 23 章：排序与归并
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter24/"
    
  >第 24 章：Hash
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter25/"
    
  >第 25 章：B-tree
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter26/"
    
  >第 26 章：GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter27/"
    
  >第 27 章：SP-GiST
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter28/"
    
  >第 28 章：GIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      sidebar-active-item hx-bg-primary-100 hx-font-semibold hx-text-primary-800 contrast-more:hx-border contrast-more:hx-border-primary-500 dark:hx-bg-primary-400/10 dark:hx-text-primary-600 contrast-more:dark:hx-border-primary-500"
    href="/docs/chapter29/"
    
  >第 29 章：BRIN
    </a></li>
          <li class="open"><a
    class="hx-flex hx-items-center hx-justify-between hx-gap-2 hx-cursor-pointer hx-rounded hx-px-2 hx-py-1.5 hx-text-sm hx-transition-colors [-webkit-tap-highlight-color:transparent] [-webkit-touch-callout:none] [word-break:break-word]
      hx-text-gray-500 hover:hx-bg-gray-100 hover:hx-text-gray-900 contrast-more:hx-border contrast-more:hx-border-transparent contrast-more:hx-text-gray-900 contrast-more:hover:hx-border-gray-900 dark:hx-text-neutral-400 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50 contrast-more:dark:hx-text-gray-50 contrast-more:dark:hover:hx-border-gray-50"
    href="/docs/chapter30/"
    
  >结论
    </a></li>
        
      </ul>
    </div>
  
  
    <div class="  hx-sticky hx-bottom-0 hx-bg-white dark:hx-bg-dark hx-mx-4 hx-py-4 hx-shadow-[0_-12px_16px_#fff] hx-flex hx-items-center hx-gap-2 dark:hx-border-neutral-800 dark:hx-shadow-[0_-12px_16px_#111] contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-shadow-none hx-border-t" data-toggle-animation="show"><div class="hx-flex hx-grow hx-flex-col"><button
  title="Change theme"
  data-theme="light"
  class="theme-toggle hx-group hx-h-7 hx-rounded-md hx-px-2 hx-text-left hx-text-xs hx-font-medium hx-text-gray-600 hx-transition-colors dark:hx-text-gray-400 hover:hx-bg-gray-100 hover:hx-text-gray-900 dark:hover:hx-bg-primary-100/5 dark:hover:hx-text-gray-50"
  type="button"
  aria-label="Change theme"
>
  <div class="hx-flex hx-items-center hx-gap-2 hx-capitalize"><svg height=12 class="group-data-[theme=light]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M12 3v1m0 16v1m9-9h-1M4 12H3m15.364 6.364l-.707-.707M6.343 6.343l-.707-.707m12.728 0l-.707.707M6.343 17.657l-.707.707M16 12a4 4 0 11-8 0 4 4 0 018 0z"/></svg><span class="group-data-[theme=light]:hx-hidden">Light</span><svg height=12 class="group-data-[theme=dark]:hx-hidden" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M20.354 15.354A9 9 0 018.646 3.646 9.003 9.003 0 0012 21a9.003 9.003 0 008.354-5.646z"/></svg><span class="group-data-[theme=dark]:hx-hidden">Dark</span></div>
</button>
</div></div></aside>
    
<nav class="hextra-toc hx-order-last hx-hidden hx-w-64 hx-shrink-0 xl:hx-block print:hx-hidden hx-px-4" aria-label="table of contents">
    <div class="hextra-scrollbar hx-sticky hx-top-16 hx-overflow-y-auto hx-pr-4 hx-pt-6 hx-text-sm [hyphens:auto] hx-max-h-[calc(100vh-var(--navbar-height)-env(safe-area-inset-bottom))] ltr:hx--mr-4 rtl:hx--ml-4"><p class="hx-mb-4 hx-font-semibold hx-tracking-tight">On this page</p><ul>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#291-%e6%80%bb%e8%a7%88">29.1 总览
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#292-%e6%a0%b7%e4%be%8b">29.2 样例
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#293-%e9%a1%b5%e9%9d%a2%e5%b8%83%e5%b1%80">29.3 页面布局
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#294-%e6%90%9c%e7%b4%a2">29.4 搜索
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#295-%e6%91%98%e8%a6%81%e4%bf%a1%e6%81%af%e6%9b%b4%e6%96%b0">29.5 摘要信息更新
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2951-%e6%8f%92%e5%85%a5%e5%80%bc">29.5.1 插入值
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2952-%e8%8c%83%e5%9b%b4%e6%8f%90%e8%a6%81">29.5.2 范围提要
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#296-minmax-%e7%b1%bb">29.6 Minmax 类
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2961-%e9%80%89%e6%8b%a9%e8%a6%81%e7%b4%a2%e5%bc%95%e7%9a%84%e5%88%97">29.6.1 选择要索引的列
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2962-%e8%8c%83%e5%9b%b4%e5%a4%a7%e5%b0%8f%e4%b8%8e%e6%90%9c%e7%b4%a2%e6%95%88%e7%8e%87">29.6.2 范围大小与搜索效率
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="ltr:hx-pl-4 rtl:hx-pr-4 hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#2963-%e5%b1%9e%e6%80%a7">29.6.3 属性
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#297-span-classmarginalia-data-notev-14minmax-multi-%e7%b1%bbspan">29.7 Minmax-Multi 类
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#298-inclusion-%e7%b1%bb">29.8 Inclusion 类
        </a>
      </li>
      <li class="hx-my-2 hx-scroll-my-6 hx-scroll-py-6">
        <a class="hx-font-semibold hx-inline-block hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-300 contrast-more:hx-text-gray-900 contrast-more:hx-underline contrast-more:dark:hx-text-gray-50 hx-w-full hx-break-words" href="#299-span-classmarginalia-data-notev-14bloom-%e7%b1%bbspan">29.9 Bloom 类
        </a>
      </li></ul>
      <div class="hx-mt-8 hx-border-t hx-bg-white hx-pt-8 hx-shadow-[0_-12px_16px_white] dark:hx-bg-dark dark:hx-shadow-[0_-12px_16px_#111] hx-sticky hx-bottom-0 hx-flex hx-flex-col hx-items-start hx-gap-2 hx-pb-8 dark:hx-border-neutral-800 contrast-more:hx-border-t contrast-more:hx-border-neutral-400 contrast-more:hx-shadow-none contrast-more:dark:hx-border-neutral-400">
        <button aria-hidden="true" id="backToTop" onClick="scrollUp();" class="hx-transition-all hx-duration-75 hx-opacity-0 hx-text-xs hx-font-medium hx-text-gray-500 hover:hx-text-gray-900 dark:hx-text-gray-400 dark:hover:hx-text-gray-100 contrast-more:hx-text-gray-800 contrast-more:dark:hx-text-gray-50">
          <span>Scroll to top</span>
          <svg xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="1.5" stroke="currentColor" class="hx-inline ltr:hx-ml-1 rtl:hx-mr-1 hx-h-3.5 hx-w-3.5 hx-border hx-rounded-full hx-border-gray-500 hover:hx-border-gray-900 dark:hx-border-gray-400 dark:hover:hx-border-gray-100 contrast-more:hx-border-gray-800 contrast-more:dark:hx-border-gray-50">
            <path stroke-linecap="round" stroke-linejoin="round" d="M4.5 15.75l7.5-7.5 7.5 7.5" />
          </svg>
        </button>
      </div>
    </div>
  </nav>


    <article class="hx-w-full hx-break-words hx-flex hx-min-h-[calc(100vh-var(--navbar-height))] hx-min-w-0 hx-justify-center hx-pb-8 hx-pr-[calc(env(safe-area-inset-right)-1.5rem)]">
      <main class="hx-w-full hx-min-w-0 hx-max-w-6xl hx-px-6 hx-pt-4 md:hx-px-12">
        
  <div class="hx-mt-1.5 hx-flex hx-items-center hx-gap-1 hx-overflow-hidden hx-text-sm hx-text-gray-500 dark:hx-text-gray-400 contrast-more:hx-text-current">
        <div class="hx-whitespace-nowrap hx-transition-colors hx-min-w-[24px] hx-overflow-hidden hx-text-ellipsis hover:hx-text-gray-900 dark:hover:hx-text-gray-100">
          <a href="/docs/">Docs</a>
        </div><svg class="hx-w-3.5 hx-shrink-0 rtl:-hx-rotate-180" xmlns="http://www.w3.org/2000/svg" fill="none" viewBox="0 0 24 24" stroke-width="2" stroke="currentColor" aria-hidden="true"><path stroke-linecap="round" stroke-linejoin="round" d="M9 5l7 7-7 7"/></svg><div class="hx-whitespace-nowrap hx-transition-colors hx-font-medium hx-text-gray-700 contrast-more:hx-font-bold contrast-more:hx-text-current dark:hx-text-gray-100 contrast-more:dark:hx-text-current">第 29 章：BRIN</div>
  </div>

        <div class="content">
          <h1>第 29 章：BRIN</h1>
          <h2>29.1 总览<span class="hx-absolute -hx-mt-20" id="291-总览"></span>
    <a href="#291-%e6%80%bb%e8%a7%88" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>与为快速找到所需行的其他索引不同，BRIN <sup id="fnref:1"><a href="#fn:1" class="footnote-ref" role="doc-noteref">1</a></sup> 旨在过滤掉不必要的行。BRIN 访问方法主要用于几个 TB 及以上的大表，因此较小的索引尺寸优于搜索精度。</p>
<p>为了加快搜索，整个表被划分为多个范围，因此得名：块范围索引。每个范围包含若干个页面。索引不存储 TIDS，只保留每个范围的摘要信息。对于 ordinal 数据类型，在最简单的情况下，是最小值和最大值，但不同的操作符类可能会收集范围中值的不同信息。</p>
<p>范围中的页面数量在创建索引时根据 <span class="marginalia" data-note="128"><em>pages_per_range</em></span> 存储参数值进行定义。</p>
<p>如果查询条件引用了索引列，那么所有保证没有匹配项的范围都可以跳过。索引将以有损位图的形式返回其他所有范围的页面；这些页面的所有行都必须重新检查。</p>
<p>因此，BRIN 非常适合具有局部化值的列 (即，存储的值彼此靠近的列具有相似的摘要信息属性) 。对于 ordinal 数据类型，这意味着值必须按升序或降序存储，也就是说，它们的物理位置与&quot;大于&quot;和&quot;小于&quot;操作定义的逻辑顺序之间具有很高的相关性。对于其他类型的摘要信息，&ldquo;相似属性&quot;可能有所不同。</p>
<p>BRIN 更像是顺序堆扫描的加速器，而不是传统意义上的索引。它可以看作是分区的一种替代方案，每个范围代表一个虚拟分区。</p>
<h2>29.2 样例<span class="hx-absolute -hx-mt-20" id="292-样例"></span>
    <a href="#292-%e6%a0%b7%e4%be%8b" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>我们的示例数据库中没有足够大到需要使用 BRIN 索引的表，但我们可以想象一下，分析报告要求我们拥有一张非规范化表，里面包含特定机场所有出发和到达航班的汇总信息，甚至到具体座位的占用情况。每个机场的数据都会在相应时区午夜时每日更新。添加的数据不会被更新或删除。</p>
<p>这张表如下所示：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">CREATE TABLE flights_bi<span class="o">(</span>
</span></span><span class="line"><span class="cl">  airport_code char<span class="o">(</span>3<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  airport_coord point, -- airport coordinates
</span></span><span class="line"><span class="cl">  airport_utc_offset interval, -- timezone
</span></span><span class="line"><span class="cl">  flight_no char<span class="o">(</span>6<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  flight_type text, -- departure or arrival
</span></span><span class="line"><span class="cl">  scheduled_time timestamptz,
</span></span><span class="line"><span class="cl">  actual_time timestamptz,
</span></span><span class="line"><span class="cl">  aircraft_code char<span class="o">(</span>3<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  seat_no varchar<span class="o">(</span>4<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  fare_conditions varchar<span class="o">(</span>10<span class="o">)</span>, -- travel class
</span></span><span class="line"><span class="cl">  passenger_id varchar<span class="o">(</span>20<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  passenger_name text
</span></span><span class="line"><span class="cl"><span class="o">)</span><span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>数据加载可以通过嵌套循环来模拟：<sup id="fnref:2"><a href="#fn:2" class="footnote-ref" role="doc-noteref">2</a></sup> 外循环对应于天数 (示例数据库中存储的是年度数据)，内循环则基于时区。因此，即使在循环中数据没有显式地进行排序，加载后的数据在时间和机场方面或多或少是有序的。</p>
<p>我将加载一个大约 4GB，包含约 3000 万行的现有数据库的副本：<sup id="fnref:3"><a href="#fn:3" class="footnote-ref" role="doc-noteref">3</a></sup></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl">postgres$ pg_restore -d demo -c flights_bi.dump
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; ANALYZE flights_bi<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT count<span class="o">(</span>*<span class="o">)</span> FROM flights_bi<span class="p">;</span>
</span></span><span class="line"><span class="cl">  count
</span></span><span class="line"><span class="cl">−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">30517076</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT pg_size_pretty<span class="o">(</span>pg_total_relation_size<span class="o">(</span><span class="s1">&#39;flights_bi&#39;</span><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> pg_size_pretty
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">4129</span> MB
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>我们无法称之为大表，但这个数据量足以展示 BRIN 的工作原理。我会提前创建一个索引：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON flights_bi USING brin<span class="o">(</span>scheduled_time<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT pg_size_pretty<span class="o">(</span>pg_total_relation_size<span class="o">(</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> pg_size_pretty
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">184</span> kB
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>默认配置下，索引占用的空间很小。</p>
<p>即使启用了<span class="marginalia" data-note="v. 13">数据去重</span>，B 树索引的大小也要大上千倍。的确，其效率也要高得多，但是对于真正的大表来说，额外的存储空间可能变成了一种无法承受的奢侈。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX flights_bi_btree_idx ON flights_bi<span class="o">(</span>scheduled_time<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT pg_size_pretty<span class="o">(</span>pg_total_relation_size<span class="o">(</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_btree_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> pg_size_pretty
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">210</span> MB
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; DROP INDEX flights_bi_btree_idx<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h2>29.3 页面布局<span class="hx-absolute -hx-mt-20" id="293-页面布局"></span>
    <a href="#293-%e9%a1%b5%e9%9d%a2%e5%b8%83%e5%b1%80" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>BRIN 索引的零页是用于保存索引结构信息的元页面。</p>
<p>在元数据的某个偏移量处是带有摘要信息的页面。此类页面中的每个索引条目都包含特定块范围的摘要信息。</p>
<p>元页面和摘要信息之间的空间由范围映射占据，有时也称为反向映射 (因此通常缩写为 revmap)。它实际上是一个指向相应索引行的指针数组；该数组中的索引号与范围号相对应。</p>
<img src="29-1.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>随着表的扩展，范围映射的大小也在增长。如果映射不适合分配的页面，它就会占用下一个页面，并且先前所有位于此页面的索引条目都会被转移到其他页面。由于一个页面可以容纳很多指针，因此这样的转移非常少见。</p>
<p>BRIN 索引页面可以像往常一样通过 pageinspect 扩展显示。元数据包括范围大小和为范围映射保留的页面数量：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT pagesperrange, lastrevmappage
</span></span><span class="line"><span class="cl">FROM brin_metapage_info<span class="o">(</span>get_raw_page<span class="o">(</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, <span class="m">0</span>
</span></span><span class="line"><span class="cl"><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> pagesperrange <span class="p">|</span> lastrevmappage
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">           <span class="m">128</span> <span class="p">|</span>              <span class="m">4</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>此处，范围映射占用了四个页面，从第一个页面到第四个页面。让我们看一下指向包含摘要数据的索引条目指针：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT *
</span></span><span class="line"><span class="cl">FROM brin_revmap_data<span class="o">(</span>get_raw_page<span class="o">(</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, <span class="m">1</span>
</span></span><span class="line"><span class="cl"><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">  pages
</span></span><span class="line"><span class="cl">−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="o">(</span>6,197<span class="o">)</span>
</span></span><span class="line"><span class="cl"> <span class="o">(</span>6,198<span class="o">)</span>
</span></span><span class="line"><span class="cl"> <span class="o">(</span>6,199<span class="o">)</span>
</span></span><span class="line"><span class="cl"> ...
</span></span><span class="line"><span class="cl"> <span class="o">(</span>6,195<span class="o">)</span>
</span></span><span class="line"><span class="cl"> <span class="o">(</span>6,196<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1360</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>如果范围还未提要，那么范围映射中的指针为空。</p>
<p>以下是几个范围的摘要信息：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT itemoffset, blknum, value
</span></span><span class="line"><span class="cl">FROM brin_page_items<span class="o">(</span>
</span></span><span class="line"><span class="cl">  get_raw_page<span class="o">(</span><span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, 6<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">ORDER BY blknum
</span></span><span class="line"><span class="cl">LIMIT <span class="m">3</span> <span class="se">\g</span>x
</span></span><span class="line"><span class="cl">−<span class="o">[</span> RECORD <span class="m">1</span> <span class="o">]</span>−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">itemoffset <span class="p">|</span> <span class="m">197</span>
</span></span><span class="line"><span class="cl">blknum     <span class="p">|</span> <span class="m">0</span>
</span></span><span class="line"><span class="cl">value      <span class="p">|</span> <span class="o">{</span>2016−08−15 02:45:00+03 .. 2016−08−15 16:20:00+03<span class="o">}</span>
</span></span><span class="line"><span class="cl">−<span class="o">[</span> RECORD <span class="m">2</span> <span class="o">]</span>−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">itemoffset <span class="p">|</span> <span class="m">198</span>
</span></span><span class="line"><span class="cl">blknum     <span class="p">|</span> <span class="m">128</span>
</span></span><span class="line"><span class="cl">value      <span class="p">|</span> <span class="o">{</span>2016−08−15 05:50:00+03 .. 2016−08−15 18:55:00+03<span class="o">}</span>
</span></span><span class="line"><span class="cl">−<span class="o">[</span> RECORD <span class="m">3</span> <span class="o">]</span>−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">itemoffset <span class="p">|</span> <span class="m">199</span>
</span></span><span class="line"><span class="cl">blknum     <span class="p">|</span> <span class="m">256</span>
</span></span><span class="line"><span class="cl">value      <span class="p">|</span> <span class="o">{</span>2016−08−15 07:15:00+03 .. 2016−08−15 18:50:00+03<span class="o">}</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h2>29.4 搜索<span class="hx-absolute -hx-mt-20" id="294-搜索"></span>
    <a href="#294-%e6%90%9c%e7%b4%a2" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>如果 BRIN 索引支持查询条件，<sup id="fnref:4"><a href="#fn:4" class="footnote-ref" role="doc-noteref">4</a></sup> 那么执行器会扫描范围映射和每个范围的摘要信息。如果某个范围内的数据可能与搜索键匹配，属于这个范围的所有页面都会被添加到位图中。由于 BRIN 不保留单独元组的 ID，所以位图总是有损的。</p>
<p>将数据与搜索键进行匹配是由 <em>consistency</em> 函数执行的，它解释了范围的摘要信息。未提要的范围始终会被添加到位图中。</p>
<p>接收到的位图将按往常方式用于扫描表。值得一提的是，堆页读取按块范围顺序进行，并采用预取机制。</p>
<h2>29.5 摘要信息更新<span class="hx-absolute -hx-mt-20" id="295-摘要信息更新"></span>
    <a href="#295-%e6%91%98%e8%a6%81%e4%bf%a1%e6%81%af%e6%9b%b4%e6%96%b0" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><h3>29.5.1 插入值<span class="hx-absolute -hx-mt-20" id="2951-插入值"></span>
    <a href="#2951-%e6%8f%92%e5%85%a5%e5%80%bc" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>当往堆页面中添加新元组时，相应索引范围的摘要信息会被更新。<sup id="fnref:5"><a href="#fn:5" class="footnote-ref" role="doc-noteref">5</a></sup> 使用简单的算术运算，根据页号计算范围号，然后通过范围映射定位摘要信息。范围编号是根据页面编号通过简单的算术运算计算得出的，然后通过范围映射定位摘要信息。</p>
<p>为了确认当前摘要信息是否需要扩展，会使用 <em>addition</em> 函数。如果需要扩展并且页面有足够的空闲空间，那么将就地进行扩展 (不添加新的索引条目)。</p>
<p>假设我们向页面 13 添加了一个值为 42 的元组。范围号是通过页号整除范围大小计算得出的。假设范围大小为四个页面，我们得到范围号 3；由于范围号是从零开始的，我们在范围映射中取第四个指针。该范围中的最小值是 31，最大值是 40。添加的值超出了这些限制，所以最大值增加了：</p>
<img src="29-2.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>如果无法就地更新，则会添加一个新条目，并且范围映射会被修改。</p>
<h3>29.5.2 范围提要<span class="hx-absolute -hx-mt-20" id="2952-范围提要"></span>
    <a href="#2952-%e8%8c%83%e5%9b%b4%e6%8f%90%e8%a6%81" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>前文所述内容适用于新元组出现在已经提要的范围内的情况。在建立索引时，所有已有的范围都会被提要，但随着表的扩大，新的页面可能会超出这些范围。</p>
<p>如果索引是在启用了 <span class="marginalia" data-note="off"><em>autosummarize</em></span> 存储参数的情况下创建的，那么新范围将立即进行提要。然而，在数据仓库中，行数据通常是批量添加，而不是逐条添加，在这种情况下，这种模式可能会严重减慢插入速度。</p>
<p>默认情况下，新范围不会立即被提要。这并不影响索引的正确性，因为没有摘要信息的范围始终会被扫描。提要是异步执行的，无论是在表清理期间还是通过调用 brin_summarize_new_values 函数 (或处理单个范围的 brin_summarize_range 函数) 来手动发起。</p>
<p>范围提要 <sup id="fnref:6"><a href="#fn:6" class="footnote-ref" role="doc-noteref">6</a></sup> 不会锁定表更新。在此过程开始时，在索引中会为这个范围插入一个占位条目。如果在扫描这个范围时，此范围内的数据发生变化，那么占位符将会被这些变更的摘要信息更新。然后 <em>union</em> 函数会将此数据与对应范围的摘要信息合并。</p>
<p>理论上，有时候在一些行被删除后，摘要信息可能会缩减。但是，虽然 GiST 索引可以在页面分裂后重新分配数据，BRIN 索引的摘要信息永远不会缩小，只会变得更宽。这里通常不需要缩减，因为数据存储通常仅用于追加新数据。你可以通过调用 brin_desummarize_range 函数手动删除该范围的摘要信息，以便再次进行提要，但没有线索表明哪些范围可能从中受益。</p>
<p>因此，BRIN 主要针对的是尺寸非常大的表，这些表要么很少有更新，通常只是将新行添加到文件末尾，要么根本就不进行更新。它主要用于在数据仓库中以建立分析报告。</p>
<h2>29.6 Minmax 类<span class="hx-absolute -hx-mt-20" id="296-minmax-类"></span>
    <a href="#296-minmax-%e7%b1%bb" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>对于允许比较值的数据类型，摘要信息至少包括最大值和最小值。相应操作符类的名称中包含 minmax 一词：<sup id="fnref:7"><a href="#fn:7" class="footnote-ref" role="doc-noteref">7</a></sup></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT opcname
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND opcname LIKE <span class="s1">&#39;%minmax_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY opcname<span class="p">;</span>
</span></span><span class="line"><span class="cl">        opcname
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> bit_minmax_ops
</span></span><span class="line"><span class="cl"> bpchar_minmax_ops
</span></span><span class="line"><span class="cl"> bytea_minmax_ops
</span></span><span class="line"><span class="cl"> char_minmax_ops
</span></span><span class="line"><span class="cl"> ...
</span></span><span class="line"><span class="cl"> timestamptz_minmax_ops
</span></span><span class="line"><span class="cl"> timetz_minmax_ops
</span></span><span class="line"><span class="cl"> uuid_minmax_ops
</span></span><span class="line"><span class="cl"> varbit_minmax_ops
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">26</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这些操作符类的支持函数如下：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amprocnum, amproc::regproc
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amproc amop ON <span class="nv">amprocfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;numeric_minmax_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amprocnum<span class="p">;</span>
</span></span><span class="line"><span class="cl"> amprocnum <span class="p">|</span>         amproc
</span></span><span class="line"><span class="cl">−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">         <span class="m">1</span> <span class="p">|</span> brin_minmax_opcinfo
</span></span><span class="line"><span class="cl">         <span class="m">2</span> <span class="p">|</span> brin_minmax_add_value
</span></span><span class="line"><span class="cl">         <span class="m">3</span> <span class="p">|</span> brin_minmax_consistent
</span></span><span class="line"><span class="cl">         <span class="m">4</span> <span class="p">|</span> brin_minmax_union
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>第一个函数返回操作符类的元数据，其他所有函数均已描述过：这些函数用于插入新值、检查一致性以及执行合并操作。</p>
<p>minmax 类包含我们在 B 树中看到的相同的比较操作符：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amopopr::regoperator, oprcode::regproc, amopstrategy
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amop amop ON <span class="nv">amopfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">  JOIN pg_operator opr ON opr.oid <span class="o">=</span> amopopr
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;numeric_minmax_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amopstrategy<span class="p">;</span>
</span></span><span class="line"><span class="cl">        amopopr      <span class="p">|</span>  oprcode   <span class="p">|</span> amopstrategy
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> &lt;<span class="o">(</span>numeric,numeric<span class="o">)</span>  <span class="p">|</span> numeric_lt <span class="p">|</span>            <span class="m">1</span>
</span></span><span class="line"><span class="cl"> &lt;<span class="o">=(</span>numeric,numeric<span class="o">)</span> <span class="p">|</span> numeric_le <span class="p">|</span>            <span class="nv">2</span>
</span></span><span class="line"><span class="cl"> <span class="o">=(</span>numeric,numeric<span class="o">)</span>  <span class="p">|</span> numeric_eq <span class="p">|</span>            <span class="m">3</span>
</span></span><span class="line"><span class="cl"> &gt;<span class="o">=(</span>numeric,numeric<span class="o">)</span> <span class="p">|</span> numeric_ge <span class="p">|</span>            <span class="m">4</span>
</span></span><span class="line"><span class="cl"> &gt;<span class="o">(</span>numeric,numeric<span class="o">)</span>  <span class="p">|</span> numeric_gt <span class="p">|</span>            <span class="m">5</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h3>29.6.1 选择要索引的列<span class="hx-absolute -hx-mt-20" id="2961-选择要索引的列"></span>
    <a href="#2961-%e9%80%89%e6%8b%a9%e8%a6%81%e7%b4%a2%e5%bc%95%e7%9a%84%e5%88%97" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>使用这个操作符类索引哪些列才是有意义的？如前所述，如果行的物理位置与值的逻辑顺序相关，那么这样的索引效果便很好。</p>
<p>让我们回顾一下上面的例子。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT attname, correlation, n_distinct
</span></span><span class="line"><span class="cl">FROM pg_stats
</span></span><span class="line"><span class="cl">WHERE <span class="nv">tablename</span> <span class="o">=</span> <span class="s1">&#39;flights_bi&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY correlation DESC NULLS LAST<span class="p">;</span>
</span></span><span class="line"><span class="cl">      attname       <span class="p">|</span>  correlation   <span class="p">|</span>   n_distinct
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> scheduled_time     <span class="p">|</span>      0.9999949 <span class="p">|</span>        <span class="m">25926</span>
</span></span><span class="line"><span class="cl"> actual_time        <span class="p">|</span>      0.9999948 <span class="p">|</span>        <span class="m">34469</span>
</span></span><span class="line"><span class="cl"> fare_conditions    <span class="p">|</span>      0.7976897 <span class="p">|</span>            <span class="m">3</span>
</span></span><span class="line"><span class="cl"> flight_type        <span class="p">|</span>      0.4981733 <span class="p">|</span>            <span class="m">2</span>
</span></span><span class="line"><span class="cl"> airport_utc_offset <span class="p">|</span>      0.4440067 <span class="p">|</span>           <span class="m">11</span>
</span></span><span class="line"><span class="cl"> aircraft_code      <span class="p">|</span>     0.19249801 <span class="p">|</span>            <span class="m">8</span>
</span></span><span class="line"><span class="cl"> airport_code       <span class="p">|</span>    0.061483838 <span class="p">|</span>          <span class="m">104</span>
</span></span><span class="line"><span class="cl"> seat_no            <span class="p">|</span>   0.0024594965 <span class="p">|</span>          <span class="m">461</span>
</span></span><span class="line"><span class="cl"> flight_no          <span class="p">|</span> 	0.0020146023 <span class="p">|</span>          <span class="m">710</span>
</span></span><span class="line"><span class="cl"> passenger_id       <span class="p">|</span> −0.00046121294 <span class="p">|</span> 2.610987e+06
</span></span><span class="line"><span class="cl"> passenger_name     <span class="p">|</span>   −0.012388787 <span class="p">|</span>         <span class="m">8618</span>
</span></span><span class="line"><span class="cl"> airport_coord      <span class="p">|</span>                <span class="p">|</span>            <span class="m">0</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">12</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>数据按时间排序 (无论是计划时间还是实际时间，几乎没有区别)：新条目按时间顺序添加，由于数据既不更新也不删除，所有的行都将依次进入表的主分支。</p>
<p>fare_conditions、flight_type 和 airport_utc_offset 这几列的相关性相对较高，但它们存储的不同值太少。</p>
<p>其他列的相关性很低，以至于使用 minmax 操作符类对它们进行索引没有任何意义。</p>
<h3>29.6.2 范围大小与搜索效率<span class="hx-absolute -hx-mt-20" id="2962-范围大小与搜索效率"></span>
    <a href="#2962-%e8%8c%83%e5%9b%b4%e5%a4%a7%e5%b0%8f%e4%b8%8e%e6%90%9c%e7%b4%a2%e6%95%88%e7%8e%87" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>可以根据用于存储特定值的页面数量来确定合适的范围大小。</p>
<p>让我们看一下 scheduled_time 列，并获取 24 小时内执飞的所有航班的信息。我们首先需要找出与此时间区间相关的数据占用了多少个表页面。</p>
<p>为了得到这个数字，我们可以基于如下事实：TID 由页号和偏移量组成。不幸的是，没有内置函数可以将 TID 分解成这两个组成部分，所以我们需要编写我们自己的笨拙函数，通过文本表示进行类型转换。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE FUNCTION tid2page<span class="o">(</span>t tid<span class="o">)</span> RETURNS integer
</span></span><span class="line"><span class="cl">LANGUAGE sql
</span></span><span class="line"><span class="cl">RETURN <span class="o">(</span>t::text::point<span class="o">)[</span>0<span class="o">]</span>::integer<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>现在我们可以观察天数是如何在表中分布的：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT min<span class="o">(</span>numblk<span class="o">)</span>, round<span class="o">(</span>avg<span class="o">(</span>numblk<span class="o">))</span> avg, max<span class="o">(</span>numblk<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT count<span class="o">(</span>distinct tid2page<span class="o">(</span>ctid<span class="o">))</span> numblk
</span></span><span class="line"><span class="cl">  FROM flights_bi
</span></span><span class="line"><span class="cl">GROUP BY scheduled_time::date
</span></span><span class="line"><span class="cl"><span class="o">)</span> t<span class="p">;</span>
</span></span><span class="line"><span class="cl"> min  <span class="p">|</span> avg  <span class="p">|</span> max
</span></span><span class="line"><span class="cl">−−−−−−+−−−−−−+−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">1192</span> <span class="p">|</span> <span class="m">1447</span> <span class="p">|</span> <span class="m">1512</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>我们可以注意到，数据分布并不完全均匀。以标准的 128 个页面的范围大小来看，每天将占用 9 到 12 个范围。在获取某一特定日期的数据时，索引扫描将返回实际需要的行，以及一些落入相同范围关联到其他日期的行。范围大小越大，读取的额外边界值就越多；我们可以通过减小或增加范围大小来改变它们的数量。</p>
<p>让我们尝试查询某一天的数据 (我已经用默认设置创建了一个索引)。为了简单起见，我将禁止并行执行：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">max_parallel_workers_per_gather</span> <span class="o">=</span> 0<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; <span class="se">\s</span>et d <span class="s1">&#39;2016-08-15 02:45:00+03&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, buffers, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE scheduled_time &gt;<span class="o">=</span> :<span class="s1">&#39;d&#39;</span>::timestamptz
</span></span><span class="line"><span class="cl">  AND scheduled_time &lt; :<span class="s1">&#39;d&#39;</span>::timestamptz + interval <span class="s1">&#39;1 day&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on flights_bi <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">81964</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">((</span>scheduled_time &gt;<span class="o">=</span> <span class="s1">&#39;2016−08−15 02:45:00+03&#39;</span>::ti...
</span></span><span class="line"><span class="cl">   Rows Removed by Index Recheck: <span class="m">11606</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">lossy</span><span class="o">=</span><span class="m">1536</span>
</span></span><span class="line"><span class="cl">   Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">1561</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on flights_bi_scheduled_time_idx
</span></span><span class="line"><span class="cl">       <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">15360</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">((</span>scheduled_time &gt;<span class="o">=</span> <span class="s1">&#39;2016−08−15 02:45:00+03&#39;</span>::...
</span></span><span class="line"><span class="cl">       Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">25</span>
</span></span><span class="line"><span class="cl"> Planning:
</span></span><span class="line"><span class="cl">   Buffers: shared <span class="nv">hit</span><span class="o">=</span><span class="m">1</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">11</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>我们可以将 BRIN 索引对某个特定查询的效率因子定义为索引扫描中跳过的页面数量与表中总的页面数量的比值。如果效率因子为零，那么索引访问会退化为顺序扫描 (不考虑额外成本)。效率因子越高，需要读取的页面就越少。但是，由于某些页面包含需要返回的数据而不能被跳过，因此效率因子总是小于 1。</p>
<p>在这个特定案例中，效率因子是 $\frac{528417 - 1561}{528417} \approx 0.997$，其中 528,417 是表中的页面数。</p>
<p>然而，我们不能基于单个值得出任何有意义的结论。即使我们拥有均匀数据和理想的相关性，效率仍会有所不同，因为至少范围边界不会与页面边界匹配。我们只有将效率因子视为一个随机值并分析其分布情况，才能获得全貌。</p>
<p>对于我们的示例，我们可以选择一年中所有不同的日子，检查每个值的执行计划，并基于这个选择计算统计数据。我们可以轻易使该过程自动化，因为 EXPLAIN 命令可以以 JSON 格式返回结果，这很便于解析。我不会在这里提供所有代码，但以下代码片段包含了所有关键细节：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; DO <span class="nv">$$</span>
</span></span><span class="line"><span class="cl">DECLARE
</span></span><span class="line"><span class="cl">  plan jsonb<span class="p">;</span>
</span></span><span class="line"><span class="cl">BEGIN
</span></span><span class="line"><span class="cl">  EXECUTE
</span></span><span class="line"><span class="cl">    <span class="s1">&#39;EXPLAIN (analyze, buffers, timing off, costs off, format json)
</span></span></span><span class="line"><span class="cl"><span class="s1">     SELECT * FROM flights_bi
</span></span></span><span class="line"><span class="cl"><span class="s1">     WHERE scheduled_time &gt;= $1
</span></span></span><span class="line"><span class="cl"><span class="s1">       AND scheduled_time &lt; $1 + interval &#39;&#39;1 day&#39;&#39;&#39;</span>
</span></span><span class="line"><span class="cl">  USING <span class="s1">&#39;2016-08-15 02:45:00+03&#39;</span>::timestamptz
</span></span><span class="line"><span class="cl">  INTO plan<span class="p">;</span>
</span></span><span class="line"><span class="cl">  RAISE NOTICE <span class="s1">&#39;shared hit=%, read=%&#39;</span>,
</span></span><span class="line"><span class="cl">    plan -&gt; <span class="m">0</span> -&gt; <span class="s1">&#39;Plan&#39;</span> -&gt;&gt; <span class="s1">&#39;Shared Hit Blocks&#39;</span>,
</span></span><span class="line"><span class="cl">    plan -&gt; <span class="m">0</span> -&gt; <span class="s1">&#39;Plan&#39;</span> -&gt;&gt; <span class="s1">&#39;Shared Read Blocks&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">END<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="nv">$$</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">NOTICE: shared <span class="nv">hit</span><span class="o">=</span>1561, <span class="nv">read</span><span class="o">=</span><span class="m">0</span>
</span></span><span class="line"><span class="cl">DO</span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>结果可以通过箱线图 (也称为&quot;盒须图&rdquo;) 来直观展示。这里的须表示第一和第四四分位 (也就是说，右须包含了 25% 的最大值，而左须包含了 25% 的最小值)。盒子本身包含剩余的 50% 的值，并标有中位数。更重要的是，这种紧凑的表示方式使我们能够直观地比较不同的结果。下图展示了默认范围大小以及其他两个范围大小 (分别是原大小的四倍和四分之一) 的效率因子分布情况。</p>
<p>正如我们所预期的，即使对于相对较大的范围，搜索的准确性和效率也很高。</p>
<p>这里的虚线标记了这个查询可能的最大效率因子的平均值，假设一天大约占表的 $\frac{1}{365}$。</p>
<img src="29-3.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>注意，效率的提升是以索引大小的增加为代价的。BRIN 在让你找到两者之间的平衡点方面相当灵活。</p>
<h3>29.6.3 属性<span class="hx-absolute -hx-mt-20" id="2963-属性"></span>
    <a href="#2963-%e5%b1%9e%e6%80%a7" class="subheading-anchor" aria-label="Permalink for this section"></a></h3><p>BRIN 的属性是固定的，不依赖于操作符类。</p>
<p><strong>访问方法属性</strong></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT a.amname, p.name, pg_indexam_has_property<span class="o">(</span>a.oid, p.name<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM pg_am a, unnest<span class="o">(</span>array<span class="o">[</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;can_order&#39;</span>, <span class="s1">&#39;can_unique&#39;</span>, <span class="s1">&#39;can_multi_col&#39;</span>,
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;can_exclude&#39;</span>, <span class="s1">&#39;can_include&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">])</span> p<span class="o">(</span>name<span class="o">)</span>
</span></span><span class="line"><span class="cl">WHERE a.amname <span class="o">=</span> <span class="s1">&#39;brin&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> amname <span class="p">|</span>     name      <span class="p">|</span> pg_indexam_has_property
</span></span><span class="line"><span class="cl">−−−−−−−−+−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> brin   <span class="p">|</span> can_order     <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> brin   <span class="p">|</span> can_unique    <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> brin   <span class="p">|</span> can_multi_col <span class="p">|</span> t
</span></span><span class="line"><span class="cl"> brin   <span class="p">|</span> can_exclude 	<span class="p">|</span> f
</span></span><span class="line"><span class="cl"> brin   <span class="p">|</span> can_include 	<span class="p">|</span> f
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>显然，BRIN 索引不支持排序或唯一性属性。由于 BRIN 索引总是返回一个位图，因此也不支持排它约束。同样，额外的 INCLUDE 列也没有意义，因为即使是索引键也不存储在 BRIN 索引中。</p>
<p>然而，我们可以创建多列 BRIN 索引。在这种情况下，每列的摘要信息被收集并存储在一个独立的索引条目中，但它们仍然有一个共同的范围映射。如果相同的范围大小适用于所有被索引的列，那么这样的索引是有用的。</p>
<p>或者，基于位图可以合并在一起的事实，我们可以为几个列创建单独的 BRIN 索引。例如：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON flights_bi USING brin<span class="o">(</span>airport_utc_offset<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE scheduled_time &gt;<span class="o">=</span> :<span class="s1">&#39;d&#39;</span>::timestamptz
</span></span><span class="line"><span class="cl">AND scheduled_time &lt; :<span class="s1">&#39;d&#39;</span>::timestamptz + interval <span class="s1">&#39;1 day&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">airport_utc_offset</span> <span class="o">=</span> <span class="s1">&#39;08:00:00&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                           QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on flights_bi <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">1658</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">((</span>scheduled_time &gt;<span class="o">=</span> <span class="s1">&#39;2016−08−15 02:45:00+03&#39;</span>::ti...
</span></span><span class="line"><span class="cl">   Rows Removed by Index Recheck: <span class="m">14077</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">lossy</span><span class="o">=</span><span class="m">256</span>
</span></span><span class="line"><span class="cl">   −&gt; BitmapAnd <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">0</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">       −&gt; Bitmap Index Scan on flights_bi_scheduled_time_idx <span class="o">(</span>act...
</span></span><span class="line"><span class="cl">           Index Cond: <span class="o">((</span>scheduled_time &gt;<span class="o">=</span> <span class="s1">&#39;2016−08−15 02:45:00+0...
</span></span></span><span class="line"><span class="cl"><span class="s1">       −&gt; Bitmap Index Scan on flights_bi_airport_utc_offset_idx ...
</span></span></span><span class="line"><span class="cl"><span class="s1">           Index Cond: (airport_utc_offset = &#39;</span>08:00:00<span class="err">&#39;</span>::interval<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">9</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p><strong>索引级属性</strong></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT p.name, pg_index_has_property<span class="o">(</span>
</span></span><span class="line"><span class="cl"><span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, p.name
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM unnest<span class="o">(</span>array<span class="o">[</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;clusterable&#39;</span>, <span class="s1">&#39;index_scan&#39;</span>, <span class="s1">&#39;bitmap_scan&#39;</span>, <span class="s1">&#39;backward_scan&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">])</span> p<span class="o">(</span>name<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">     name      <span class="p">|</span> pg_index_has_property
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> clusterable   <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> index_scan    <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> bitmap_scan   <span class="p">|</span> t
</span></span><span class="line"><span class="cl"> backward_scan <span class="p">|</span> f
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">4</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>显然，位图扫描是唯一支持的访问类型。</p>
<p>缺乏聚簇可能看起来令人困惑。由于 BRIN 对行的物理顺序敏感，逻辑上可以假设它应当支持重新排序，以最大限度地提高效率。但是，考虑到重建表所需的所有处理和额外的磁盘空间，对大表进行聚簇无论如何都是一种奢侈。此外，正如 flights_bi 表的例子所示，数据存储中的某种排序可以自然发生。</p>
<p><strong>列级属性</strong></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT p.name, pg_index_column_has_property<span class="o">(</span>
</span></span><span class="line"><span class="cl"><span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, 1, p.name
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM unnest<span class="o">(</span>array<span class="o">[</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;orderable&#39;</span>, <span class="s1">&#39;distance_orderable&#39;</span>, <span class="s1">&#39;returnable&#39;</span>,
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;search_array&#39;</span>, <span class="s1">&#39;search_nulls&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">])</span> p<span class="o">(</span>name<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">        name        <span class="p">|</span> pg_index_column_has_property
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> orderable          <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> distance_orderable <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> returnable         <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> search_array       <span class="p">|</span> f
</span></span><span class="line"><span class="cl"> search_nulls       <span class="p">|</span> t
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">5</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>唯一可用的列级属性是对空值的支持。为了追踪某个范围内的空值，摘要信息提供了一个单独的属性：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT hasnulls, allnulls, value
</span></span><span class="line"><span class="cl">FROM brin_page_items<span class="o">(</span>
</span></span><span class="line"><span class="cl">  get_raw_page<span class="o">(</span><span class="s1">&#39;flights_bi_airport_utc_offset_idx&#39;</span>, 6<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_airport_utc_offset_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">WHERE <span class="nv">itemoffset</span><span class="o">=</span> 1<span class="p">;</span>
</span></span><span class="line"><span class="cl"> hasnulls <span class="p">|</span> allnulls <span class="p">|</span>         value
</span></span><span class="line"><span class="cl">−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> f        <span class="p">|</span> f        <span class="p">|</span> <span class="o">{</span>03:00:00 .. 03:00:00<span class="o">}</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h2>29.7 <span class="marginalia" data-note="v. 14"><strong>Minmax-Multi 类</strong></span><span class="hx-absolute -hx-mt-20" id="297-span-classmarginalia-data-notev-14minmax-multi-类span"></span>
    <a href="#297-span-classmarginalia-data-notev-14minmax-multi-%e7%b1%bbspan" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>数据更新很容易打乱已建立的相关性。问题不在于特定值的实际修改，而在于 MVCC 设计本身：旧版本的行可能在一个页面中被删除，而其新版本可以插入到当前空闲的任何位置，因此无法保持原始行顺序。为了在一定程度上最小化这种影响，我们可以降低 fillfactor 存储参数值，为后续的更新留出更多页面空间。但是，真的值得增加已经很大的表的大小吗？此外，删除操作无论如何都会在现有页面中释放出一些空间，从而为新元组设下陷阱，这些元组本应被添加到文件的末尾。</p>
<p>这种情况可以很容易地被模拟。让我们删除随机选择的 0.1% 比例的行，并对表进行清理操作，以清理一些空间供新元组使用：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; WITH t AS <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT ctid
</span></span><span class="line"><span class="cl">  FROM flights_bi TABLESAMPLE BERNOULLI<span class="o">(</span>0.1<span class="o">)</span> REPEATABLE<span class="o">(</span>0<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">DELETE FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE ctid IN <span class="o">(</span>SELECT ctid FROM t<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">DELETE <span class="nv">30180</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; VACUUM flights_bi<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>现在，让我们在其中一个时区为新的一天添加一些数据。我将简单地复制前一天的数据：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; INSERT INTO flights_bi
</span></span><span class="line"><span class="cl">SELECT airport_code, airport_coord, airport_utc_offset,
</span></span><span class="line"><span class="cl">  flight_no, flight_type, scheduled_time + interval <span class="s1">&#39;1 day&#39;</span>,
</span></span><span class="line"><span class="cl">  actual_time + interval <span class="s1">&#39;1 day&#39;</span>, aircraft_code, seat_no,
</span></span><span class="line"><span class="cl"> fare_conditions, passenger_id, passenger_name
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE date_trunc<span class="o">(</span><span class="s1">&#39;day&#39;</span>, scheduled_time<span class="o">)</span> <span class="o">=</span> <span class="s1">&#39;2017-08-15&#39;</span>
</span></span><span class="line"><span class="cl">  AND <span class="nv">airport_utc_offset</span> <span class="o">=</span> <span class="s1">&#39;03:00:00&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">INSERT <span class="m">0</span> <span class="m">40532</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>执行的删除操作足以在所有或几乎所有的范围内释放一些空间。新元组被插入到位于文件中间某处的页面，自动扩展了范围。例如，与第一个范围相关的摘要信息过去可能覆盖的时间少于一天，但现在它包含了整个年份：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT value
</span></span><span class="line"><span class="cl">FROM brin_page_items<span class="o">(</span>
</span></span><span class="line"><span class="cl">  get_raw_page<span class="o">(</span><span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>, 6<span class="o">)</span>,
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_scheduled_time_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">WHERE <span class="nv">blknum</span> <span class="o">=</span> 0<span class="p">;</span>
</span></span><span class="line"><span class="cl">                       value
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="o">{</span>2016−08−15 02:45:00+03 .. 2017−08−16 09:35:00+03<span class="o">}</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>查询中指定的日期越小，需要扫描的范围就越多。下图显示了这一灾难的严重程度：</p>
<img src="29-4.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<p>为了解决这个问题，我们需要使摘要信息更加复杂一些：不再存储单个连续的范围，而是需要存储多个较小的范围，这些范围加起来覆盖所有的值。然后，其中一个范围可以覆盖主要的数据集，而其余的则处理偶尔的异常值。</p>
<p>这样的功能由 minmax-multi 操作符类提供：<sup id="fnref:8"><a href="#fn:8" class="footnote-ref" role="doc-noteref">8</a></sup></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT opcname
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND opcname LIKE <span class="s1">&#39;%minmax_multi_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY opcname<span class="p">;</span>
</span></span><span class="line"><span class="cl">           opcname
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> date_minmax_multi_ops
</span></span><span class="line"><span class="cl"> float4_minmax_multi_ops
</span></span><span class="line"><span class="cl"> float8_minmax_multi_ops
</span></span><span class="line"><span class="cl"> inet_minmax_multi_ops
</span></span><span class="line"><span class="cl"> ...
</span></span><span class="line"><span class="cl"> time_minmax_multi_ops
</span></span><span class="line"><span class="cl"> timestamp_minmax_multi_ops
</span></span><span class="line"><span class="cl"> timestamptz_minmax_multi_ops
</span></span><span class="line"><span class="cl"> timetz_minmax_multi_ops
</span></span><span class="line"><span class="cl"> uuid_minmax_multi_ops
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">19</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>与 minmax 操作符类相比，minmax-multi 类有一个额外的支持函数，用于计算值之间的距离；这个函数用于确定范围长度，操作符类则努力减少这一长度。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amprocnum, amproc::regproc
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amproc amop ON <span class="nv">amprocfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;numeric_minmax_multi_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amprocnum<span class="p">;</span>
</span></span><span class="line"><span class="cl"> amprocnum <span class="p">|</span>               amproc
</span></span><span class="line"><span class="cl">−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">         <span class="m">1</span> <span class="p">|</span> brin_minmax_multi_opcinfo
</span></span><span class="line"><span class="cl">         <span class="m">2</span> <span class="p">|</span> brin_minmax_multi_add_value
</span></span><span class="line"><span class="cl">         <span class="m">3</span> <span class="p">|</span> brin_minmax_multi_consistent
</span></span><span class="line"><span class="cl">         <span class="m">4</span> <span class="p">|</span> brin_minmax_multi_union
</span></span><span class="line"><span class="cl">         <span class="m">5</span> <span class="p">|</span> brin_minmax_multi_options
</span></span><span class="line"><span class="cl">        <span class="m">11</span> <span class="p">|</span> brin_minmax_multi_distance_numeric
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>这类操作符的操作与 minmax 类的操作完全相同。</p>
<p>Minmax-multi 类可以接受 <span class="marginalia" data-note="32"><em>values_per_range</em></span> 参数，该参数定义了每个范围所允许的摘要值的最大数量。一个摘要值由两个数字 (一个区间) 表示，而单个点只需要一个数字。如果值不够，一些区间会缩小。<sup id="fnref:9"><a href="#fn:9" class="footnote-ref" role="doc-noteref">9</a></sup></p>
<p>让我们构建一个 minmax-multi 索引来替换现有的索引。我们将每个范围允许的值的数量限制为 16：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; DROP INDEX flights_bi_scheduled_time_idx<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON flights_bi USING brin<span class="o">(</span>
</span></span><span class="line"><span class="cl">  scheduled_time timestamptz_minmax_multi_ops<span class="o">(</span>
</span></span><span class="line"><span class="cl">    <span class="nv">values_per_range</span> <span class="o">=</span> <span class="m">16</span>
</span></span><span class="line"><span class="cl">  <span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span><span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>图表显示，新索引将效率恢复到了原始水平。不出所料，这也导致了索引大小的增加：</p>
<img src="29-5.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<h2>29.8 Inclusion 类<span class="hx-absolute -hx-mt-20" id="298-inclusion-类"></span>
    <a href="#298-inclusion-%e7%b1%bb" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>minmax 操作符类与 inclusion 操作符类之间的区别，大致上与 B 树和 GiST 索引之间的区别相似：后者用于不支持比较操作的数据类型，尽管对于它们而言，值的相互对齐仍然有意义。由 inclusion 操作符类提供的特定范围内的摘要信息，由该范围内值的边界框表示。</p>
<p>此处是这些操作符类；数量并不多：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT opcname
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND opcname LIKE <span class="s1">&#39;%inclusion_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY opcname<span class="p">;</span>
</span></span><span class="line"><span class="cl">       opcname
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> box_inclusion_ops
</span></span><span class="line"><span class="cl"> inet_inclusion_ops
</span></span><span class="line"><span class="cl"> range_inclusion_ops
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">3</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>支持函数的列表通过增加一个必需的函数来扩展，该函数用于合并两个值，以及一系列可选的函数：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amprocnum, amproc::regproc
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amproc amop ON <span class="nv">amprocfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;box_inclusion_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amprocnum<span class="p">;</span>
</span></span><span class="line"><span class="cl"> amprocnum <span class="p">|</span>          amproc
</span></span><span class="line"><span class="cl">−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">         <span class="m">1</span> <span class="p">|</span> brin_inclusion_opcinfo			
</span></span><span class="line"><span class="cl">         <span class="m">2</span> <span class="p">|</span> brin_inclusion_add_value
</span></span><span class="line"><span class="cl">         <span class="m">3</span> <span class="p">|</span> brin_inclusion_consistent
</span></span><span class="line"><span class="cl">         <span class="m">4</span> <span class="p">|</span> brin_inclusion_union
</span></span><span class="line"><span class="cl">        <span class="m">11</span> <span class="p">|</span> bound_box
</span></span><span class="line"><span class="cl">        <span class="m">13</span> <span class="p">|</span> box_contain
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>在处理可以比较的值时，我们依赖于它们的相关性；但对于其他数据类型，不会收集这样的统计信息，<sup id="fnref:10"><a href="#fn:10" class="footnote-ref" role="doc-noteref">10</a></sup> 因此很难预测基于 inclusion 的 BRIN 索引的效率。</p>
<p>更糟糕的是，相关性极大地影响了索引扫描的成本估算。如果这样的统计信息不可用，它将被视为零。<sup id="fnref:11"><a href="#fn:11" class="footnote-ref" role="doc-noteref">11</a></sup> 因此，规划器无法区分精确和模糊的 inclusion 索引，所以通常会避免使用它们。</p>
<blockquote>
<p>PostGIS 会<span class="marginalia" data-note="v. 3.1.1">收集空间数据相关性的统计信息</span>。</p>
</blockquote>
<p>在此特例下，我们可以假设在机场坐标上建立索引是有意义的，因为经度必须与时区相关联。</p>
<p>与 GiST 谓词不同，BRIN 摘要信息与被索引数据具有相同的类型；因此，为点构建索引并不那么容易。但是，我们可以通过将点转换成虚拟矩形来创建一个表达式索引。</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON flights_bi USING brin<span class="o">(</span>box<span class="o">(</span>airport_coord<span class="o">))</span>
</span></span><span class="line"><span class="cl">WITH <span class="o">(</span><span class="nv">pages_per_range</span> <span class="o">=</span> 8<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT pg_size_pretty<span class="o">(</span>pg_total_relation_size<span class="o">(</span>
</span></span><span class="line"><span class="cl">  <span class="s1">&#39;flights_bi_box_idx&#39;</span>
</span></span><span class="line"><span class="cl"><span class="o">))</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> pg_size_pretty
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="m">3816</span> kB
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>在时区上建立的索引，如果范围大小相同，大约占用相同的大小 (3288 kB)。</p>
<p>这个类中包含的操作符类似于 GiST 操作符。例如，BRIN 索引可以用来加速在特定区域内点的搜索：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT airport_code, airport_name
</span></span><span class="line"><span class="cl">FROM airports
</span></span><span class="line"><span class="cl">WHERE box<span class="o">(</span>coordinates<span class="o">)</span> &lt;@ box <span class="s1">&#39;135,45,140,50&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"> airport_code <span class="p">|</span>      airport_name
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> KHV          <span class="p">|</span> Khabarovsk−Novy Airport
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>但如前所述，除非我们关闭顺序扫描，否则规划器拒绝使用索引扫描：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>costs off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE box<span class="o">(</span>airport_coord<span class="o">)</span> &lt;@ box <span class="s1">&#39;135,45,140,50&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                         QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Seq Scan on flights_bi
</span></span><span class="line"><span class="cl">   Filter: <span class="o">(</span>box<span class="o">(</span>airport_coord<span class="o">)</span> &lt;@ <span class="s1">&#39;(140,50),(135,45)&#39;</span>::box<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">2</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl">
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; SET <span class="nv">enable_seqscan</span> <span class="o">=</span> off<span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE box<span class="o">(</span>airport_coord<span class="o">)</span> &lt;@ box <span class="s1">&#39;135,45,140,50&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                            QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on flights_bi <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">511414</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span>box<span class="o">(</span>airport_coord<span class="o">)</span> &lt;@ <span class="s1">&#39;(140,50),(135,45)&#39;</span>::box<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Rows Removed by Index Recheck: <span class="m">630756</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">lossy</span><span class="o">=</span><span class="m">19656</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on flights_bi_box_idx <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span>196560...
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span>box<span class="o">(</span>airport_coord<span class="o">)</span> &lt;@ <span class="s1">&#39;(140,50),(135,45)&#39;</span>::box<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET enable_seqscan<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<h2>29.9 <span class="marginalia" data-note="v. 14"><strong>Bloom 类</strong></span><span class="hx-absolute -hx-mt-20" id="299-span-classmarginalia-data-notev-14bloom-类span"></span>
    <a href="#299-span-classmarginalia-data-notev-14bloom-%e7%b1%bbspan" class="subheading-anchor" aria-label="Permalink for this section"></a></h2><p>基于布隆过滤器的操作符类允许对任何支持等于操作且定义了哈希函数的数据类型使用 BRIN 索引。如果值分布在不同范围内且它们的物理位置与逻辑顺序没有相关性，那么也可以将这些操作符类应用于常规的 ordinal 数据类型。</p>
<p>这些操作符类的名称中包含 &ldquo;bloom&rdquo; 一词：<sup id="fnref:12"><a href="#fn:12" class="footnote-ref" role="doc-noteref">12</a></sup></p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT opcname
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND opcname LIKE <span class="s1">&#39;%bloom_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY opcname<span class="p">;</span>
</span></span><span class="line"><span class="cl">        opcname
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> bpchar_bloom_ops
</span></span><span class="line"><span class="cl"> bytea_bloom_ops
</span></span><span class="line"><span class="cl"> char_bloom_ops
</span></span><span class="line"><span class="cl"> ...
</span></span><span class="line"><span class="cl"> timestamptz_bloom_ops
</span></span><span class="line"><span class="cl"> timetz_bloom_ops
</span></span><span class="line"><span class="cl"> uuid_bloom_ops
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">24</span> rows<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>布隆过滤器是一种经典的数据结构，它能够快速检查一个元素是否属于某个集合。这个过滤器非常紧凑，但它允许误报 (false positives)：可能会误认为集合中包含比实际更多的元素。然而，更重要的是，它避免了漏报 (false negative)：如果元素实际存在于集合中，过滤器不会认为它不存在。</p>
<p>这种过滤器是由 <em>m</em> 个比特位组成的数组 (也称为签名)，初始时这些位都被置为零。我们选定 <em>k</em> 个不同的哈希函数，将集合中的任何元素映射到签名的 <em>k</em> 个位上。当一个元素被添加到集合中时，签名中的每一位都会被设置为一。因此，如果与某个元素对应的所有位都被设置为一，则该元素可能存在于集合中；如果至少有一位为零，则可以保证该元素不存在。</p>
<p>在 BRIN 索引的情况下，过滤器处理属于特定范围的索引列的一组值；该范围的摘要信息由构建的布隆过滤器表示。</p>
<blockquote>
<p>bloom 扩展 <sup id="fnref:13"><a href="#fn:13" class="footnote-ref" role="doc-noteref">13</a></sup> 提供了一种基于布隆过滤器的索引访问方法。它为每行数据构建一个过滤器，并处理每行的一组列值。这样的索引旨在一次索引多列，并且可以用于临时查询，这种查询中过滤条件里所引用的列是事先未知的。BRIN 索引也可以建立在多个列上，但其摘要信息将包含为这些列中的每一个列构建的独立的布隆过滤器。</p>
</blockquote>
<p>布隆过滤器的准确性取决于签名长度。在理论上，可以估算出最优的签名位数 $m = \frac{-n \log_{2} p}{\ln 2}$，其中 n 是集合中元素的数量，p 是发生误报的概率。这两个设置可以通过相应的操作符类参数进行调整：</p>
<ul>
<li><span class="marginalia" data-note="-0.1"><em>n_distinct_per_range</em></span> 定义了集合中元素的数量；在这种情况下，它是索引列的一个范围中不同值的数量。这个参数值的解释就像对不同值的统计一样：负值表示范围中行的比例，而不是它们的绝对数量。</li>
<li><span class="marginalia" data-note="0.01"><em>false_positive_rate</em></span> 定义了发生误报的概率。一个接近零的值意味着索引扫描几乎肯定会跳过没有被搜索值的范围。但这并不保证精确搜索，因为被扫描的范围还将包含不匹配查询的额外行。这种行为是由于范围宽度和物理数据位置造成的，而不是由于实际的过滤器属性。</li>
</ul>
<p>支持函数列表增加了一个哈希函数：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amprocnum, amproc::regproc
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amproc amop ON <span class="nv">amprocfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;numeric_bloom_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amprocnum<span class="p">;</span>
</span></span><span class="line"><span class="cl"> amprocnum <span class="p">|</span>        amproc
</span></span><span class="line"><span class="cl">−−−−−−−−−−−+−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl">         <span class="m">1</span> <span class="p">|</span> brin_bloom_opcinfo
</span></span><span class="line"><span class="cl">         <span class="m">2</span> <span class="p">|</span> brin_bloom_add_value
</span></span><span class="line"><span class="cl">         <span class="m">3</span> <span class="p">|</span> brin_bloom_consistent
</span></span><span class="line"><span class="cl">         <span class="m">4</span> <span class="p">|</span> brin_bloom_union
</span></span><span class="line"><span class="cl">         <span class="m">5</span> <span class="p">|</span> brin_bloom_options
</span></span><span class="line"><span class="cl">        <span class="m">11</span> <span class="p">|</span> hash_numeric
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span>	</span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>由于布隆过滤器基于哈希运算，因此它只支持等值操作符：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT amopopr::regoperator, oprcode::regproc, amopstrategy
</span></span><span class="line"><span class="cl">FROM pg_am am
</span></span><span class="line"><span class="cl">  JOIN pg_opclass opc ON <span class="nv">opcmethod</span> <span class="o">=</span> am.oid
</span></span><span class="line"><span class="cl">  JOIN pg_amop amop ON <span class="nv">amopfamily</span> <span class="o">=</span> opcfamily
</span></span><span class="line"><span class="cl">  JOIN pg_operator opr ON opr.oid <span class="o">=</span> amopopr
</span></span><span class="line"><span class="cl">WHERE <span class="nv">amname</span> <span class="o">=</span> <span class="s1">&#39;brin&#39;</span>
</span></span><span class="line"><span class="cl">AND <span class="nv">opcname</span> <span class="o">=</span> <span class="s1">&#39;numeric_bloom_ops&#39;</span>
</span></span><span class="line"><span class="cl">ORDER BY amopstrategy<span class="p">;</span>
</span></span><span class="line"><span class="cl">      amopopr       <span class="p">|</span>  oprcode   <span class="p">|</span> amopstrategy
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−+−−−−−−−−−−−−+−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> <span class="o">=(</span>numeric,numeric<span class="o">)</span> <span class="p">|</span> numeric_eq <span class="p">|</span>            <span class="m">1</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>让我们以存储航班号的 flight_no 列为例；它几乎没有相关性，因此对于常规范围操作符类来说是无用的。我们将保持默认的 false-positive 设置；至于一个范围内的不同值数量，可以很容易地计算出来。例如，对于一个八页的范围，我们将得到以下的值：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; SELECT max<span class="o">(</span>nd<span class="o">)</span>
</span></span><span class="line"><span class="cl">FROM <span class="o">(</span>
</span></span><span class="line"><span class="cl">  SELECT count<span class="o">(</span>distinct flight_no<span class="o">)</span> nd
</span></span><span class="line"><span class="cl">    FROM flights_bi
</span></span><span class="line"><span class="cl">GROUP BY tid2page<span class="o">(</span>ctid<span class="o">)</span> / <span class="m">8</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span> t<span class="p">;</span>
</span></span><span class="line"><span class="cl"> max
</span></span><span class="line"><span class="cl">−−−−−
</span></span><span class="line"><span class="cl">  <span class="m">22</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">1</span> row<span class="o">)</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>对于较小的范围，这个数字甚至会更低 (但无论如何，操作符类不允许小于 16 的值)。</p>
<p>我们只需创建一个索引并检查执行计划：</p>
<div class="hextra-code-block hx-relative hx-mt-6 first:hx-mt-0 hx-group/code">
  

<div><div class="highlight"><pre tabindex="0" class="chroma"><code class="language-bash" data-lang="bash"><span class="line"><span class="cl"><span class="o">=</span>&gt; CREATE INDEX ON flights_bi USING brin<span class="o">(</span>
</span></span><span class="line"><span class="cl">  flight_no bpchar_bloom_ops<span class="o">(</span>
</span></span><span class="line"><span class="cl">    <span class="nv">n_distinct_per_range</span> <span class="o">=</span> 22<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">)</span>
</span></span><span class="line"><span class="cl">WITH <span class="o">(</span><span class="nv">pages_per_range</span> <span class="o">=</span> 8<span class="o">)</span><span class="p">;</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; EXPLAIN <span class="o">(</span>analyze, costs off, timing off, summary off<span class="o">)</span>
</span></span><span class="line"><span class="cl">SELECT *
</span></span><span class="line"><span class="cl">FROM flights_bi
</span></span><span class="line"><span class="cl">WHERE <span class="nv">flight_no</span> <span class="o">=</span> <span class="s1">&#39;PG0001&#39;</span><span class="p">;</span>
</span></span><span class="line"><span class="cl">                             QUERY PLAN
</span></span><span class="line"><span class="cl">−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
</span></span><span class="line"><span class="cl"> Bitmap Heap Scan on flights_bi <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span><span class="m">5192</span> <span class="nv">loops</span><span class="o">=</span>1<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Recheck Cond: <span class="o">(</span><span class="nv">flight_no</span> <span class="o">=</span> <span class="s1">&#39;PG0001&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl">   Rows Removed by Index Recheck: <span class="m">122894</span>
</span></span><span class="line"><span class="cl">   Heap Blocks: <span class="nv">lossy</span><span class="o">=</span><span class="m">2168</span>
</span></span><span class="line"><span class="cl">   −&gt; Bitmap Index Scan on flights_bi_flight_no_idx <span class="o">(</span>actual <span class="nv">rows</span><span class="o">=</span>...
</span></span><span class="line"><span class="cl">       Index Cond: <span class="o">(</span><span class="nv">flight_no</span> <span class="o">=</span> <span class="s1">&#39;PG0001&#39;</span>::bpchar<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">(</span><span class="m">6</span> rows<span class="o">)</span>
</span></span><span class="line"><span class="cl"><span class="o">=</span>&gt; RESET max_parallel_workers_per_gather<span class="p">;</span></span></span></code></pre></div></div><div class="hextra-code-copy-btn-container hx-opacity-0 hx-transition group-hover/code:hx-opacity-100 hx-flex hx-gap-1 hx-absolute hx-m-[11px] hx-right-0 hx-top-0">
  <button
    class="hextra-code-copy-btn hx-group/copybtn hx-transition-all active:hx-opacity-50 hx-bg-primary-700/5 hx-border hx-border-black/5 hx-text-gray-600 hover:hx-text-gray-900 hx-rounded-md hx-p-1.5 dark:hx-bg-primary-300/10 dark:hx-border-white/10 dark:hx-text-gray-400 dark:hover:hx-text-gray-50"
    title="Copy code"
  >
    <div class="copy-icon group-[.copied]/copybtn:hx-hidden hx-pointer-events-none hx-h-4 hx-w-4"></div>
    <div class="success-icon hx-hidden group-[.copied]/copybtn:hx-block hx-pointer-events-none hx-h-4 hx-w-4"></div>
  </button>
</div>

  
</div>
<p>图表显示，对于某些航班号 (由不属于任何须部分的单独点表示)，索引的工作效果不是很好，但总体效率相当高：</p>
<img src="29-6.png" style="width:70%; float:left" />
<div style="clear:both;"></div>
<div class="footnotes" role="doc-endnotes">
<hr>
<ol>
<li id="fn:1">
<p>postgresql.org/docs/14/brin.html<br>backend/access/brin/README&#160;<a href="#fnref:1" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:2">
<p>edu.postgrespro.ru/internals-14/flights_bi.sql&#160;<a href="#fnref:2" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:3">
<p>edu.postgrespro.ru/internals-\oldstylenums{14}/flights_bi.dump.&#160;<a href="#fnref:3" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:4">
<p>backend/access/brin/brin.c, bringetbitmap function&#160;<a href="#fnref:4" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:5">
<p>backend/access/brin/brin.c, brininsert function&#160;<a href="#fnref:5" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:6">
<p>backend/access/brin/brin.c, summarize_range function&#160;<a href="#fnref:6" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:7">
<p>backend/access/brin/brin_minmax.c&#160;<a href="#fnref:7" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:8">
<p>backend/access/brin/brin_minmax_multi.c&#160;<a href="#fnref:8" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:9">
<p>backend/access/brin/brin_minmax_multi.c, reduce_expanded_ranges function&#160;<a href="#fnref:9" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:10">
<p>backend/commands/analyze.c, compute_scalar_stats function&#160;<a href="#fnref:10" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:11">
<p>backend/utils/adt/selfuncs.c, brincostestimate function&#160;<a href="#fnref:11" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:12">
<p>backend/access/brin/brin_bloom.c&#160;<a href="#fnref:12" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
<li id="fn:13">
<p>postgresql.org/docs/14/bloom.html&#160;<a href="#fnref:13" class="footnote-backref" role="doc-backlink">&#x21a9;&#xfe0e;</a></p>
</li>
</ol>
</div>

        </div>
        <div class="hx-mt-16"></div>
        
        
      </main>
    </article>
  </div>

      <footer class="hextra-footer hx-bg-gray-100 hx-pb-[env(safe-area-inset-bottom)] dark:hx-bg-neutral-900 print:hx-bg-transparent"><div
    class="hx-max-w-screen-xl hx-mx-auto hx-flex hx-justify-center hx-py-12 hx-pl-[max(env(safe-area-inset-left),1.5rem)] hx-pr-[max(env(safe-area-inset-right),1.5rem)] hx-text-gray-600 dark:hx-text-gray-400 md:hx-justify-start"
  >
    <div class="hx-flex hx-w-full hx-flex-col hx-items-center sm:hx-items-start"><div class="hx-mt-6 hx-text-xs">本中文译文版权归熊灿灿所有。英文原文版权归 Postgres Professional 所有。本译文根据授权翻译与发布。</div>
    </div>
  </div>
</footer>
    
    <script defer src="/js/main.js" integrity=""></script>


<script defer src="/lib/flexsearch/flexsearch.bundle.min.0425860527cc9968f9f049421c7a56b39327d475e2e3a8f550416be3a9134327.js" integrity="sha256-BCWGBSfMmWj58ElCHHpWs5Mn1HXi46j1UEFr46kTQyc="></script>
    <script defer src="/en.search.js" integrity=""></script>
<link type="text/css" rel="stylesheet" href="/lib/katex/katex.min.7e5db7914b97e596a36c1abb67ccc7f174f8bb71d38c9a88c55b262ed1737f97.css" integrity="sha256-fl23kUuX5ZajbBq7Z8zH8XT4u3HTjJqIxVsmLtFzf5c=" />
  <script defer src="/lib/katex/katex.min.9f45307c5794ed247a0d095f3a62e52ef2215a67b2327203a7fd919959ae79d1.js" integrity="sha256-n0UwfFeU7SR6DQlfOmLlLvIhWmeyMnIDp/2RmVmuedE="></script>
  <script defer src="/lib/katex/auto-render.min.7b57d427ac6270677daf8d8380ded2cc73336f9149a167b8e1fe0d6ef66604ae.js" integrity="sha256-e1fUJ6xicGd9r42DgN7SzHMzb5FJoWe44f4NbvZmBK4="></script>
  <script defer src="/lib/katex/mhchem.min.f0ca03df194b8c3d6017ff455db6a0ef98857905663fa311a6cded788b15340b.js" integrity="sha256-8MoD3xlLjD1gF/9FXbag75iFeQVmP6MRps3teIsVNAs="></script>
  <script>
    
    
    document.addEventListener("DOMContentLoaded", function () {
      renderMathInElement(document.body, {
        delimiters: [
          { left: "$$", right: "$$", display: true },
          { left: "$", right: "$", display: false },
          { left: "\\(", right: "\\)", display: false },
          { left: "\\begin{equation}", right: "\\end{equation}", display: true },
          {left: "\\begin{align}", right: "\\end{align}", display: true},
          {left: "\\begin{alignat}", right: "\\end{alignat}", display: true},
          {left: "\\begin{gather}", right: "\\end{gather}", display: true},
          {left: "\\begin{CD}", right: "\\end{CD}", display: true},
          { left: "\\[", right: "\\]", display: true },
        ],
        throwOnError: false,
      });
    });
  </script>


  </body>
</html>
